﻿Imports System.Data
Imports System.Data.SqlClient
Public Class PessoaDAL
    Public ObjConexao As New Conexao

#Region "Incluir/Alterar"
    Public Function InserirAlterar(Codigo As Integer, Nome As String, Telefone As String, Celular As String, Email As String, Endereco As String, Cep As String, Cidade As String, Uf As String, Bairro As String, usuario As String) As Integer

        Dim cmd = New SqlCommand
        Try
            cmd.CommandType = CommandType.StoredProcedure
            cmd.CommandText = "SPPessoa"
            cmd.Parameters.Add(New SqlParameter("@codigo", SqlDbType.Int)).Value = Codigo
            cmd.Parameters.Add(New SqlParameter("@nome", SqlDbType.VarChar)).Value = Nome
            cmd.Parameters.Add(New SqlParameter("@telefone", SqlDbType.VarChar)).Value = Telefone
            cmd.Parameters.Add(New SqlParameter("@celular", SqlDbType.VarChar)).Value = Celular
            cmd.Parameters.Add(New SqlParameter("@email", SqlDbType.VarChar)).Value = Email
            cmd.Parameters.Add(New SqlParameter("@endereco", SqlDbType.VarChar)).Value = Endereco
            cmd.Parameters.Add(New SqlParameter("@cep", SqlDbType.VarChar)).Value = Cep
            cmd.Parameters.Add(New SqlParameter("@cidade", SqlDbType.VarChar)).Value = Cidade
            cmd.Parameters.Add(New SqlParameter("@uf", SqlDbType.VarChar)).Value = Uf
            cmd.Parameters.Add(New SqlParameter("@bairro", SqlDbType.VarChar)).Value = Bairro
            If (Not IsNothing(usuario)) Then
                cmd.Parameters.Add(New SqlParameter("@usuario", SqlDbType.VarChar)).Value = usuario
            Else
                cmd.Parameters.Add(New SqlParameter("@usuario", SqlDbType.VarChar)).Value = ""
            End If
            Codigo = ObjConexao.executaCmd(cmd)
            Return Codigo
        Catch ex As Exception
            Throw ex
        End Try
    End Function
#End Region

#Region "Valida exclusão"
    Public Function ValidaExcluir(ByVal Codigo As Integer) As Boolean
        Dim cmd = New SqlCommand
        Dim tab As DataTable
        Dim ret As Boolean = True
        Try
            cmd.CommandText = "select*from Mul_Pessoa p inner join Mul_Coleta c on p.pes_codigo=c.pes_codigo where p.pes_codigo=@codigo "
            cmd.Parameters.Add(New SqlParameter("@codigo", SqlDbType.Int)).Value = Codigo
            tab = ObjConexao.executaConsulta(cmd)
            If (tab.Rows.Count > 0) Then
                ret = False
            End If
        Catch ex As Exception
            Throw ex
        Finally
            cmd = Nothing
        End Try
        Return ret
    End Function
#End Region

#Region "Excluir"
    Public Function Excluir(Codigo As Integer) As Integer
        Dim cmd = New SqlCommand
        Try
            cmd.CommandText = "Delete from Mul_Pessoa where pes_codigo=@codigo"
            cmd.Parameters.Add(New SqlParameter("@codigo", SqlDbType.Int)).Value = Codigo
            Codigo = ObjConexao.executaCmd(cmd)
            Return Codigo
        Catch ex As Exception
            Throw ex
        Finally
            cmd = Nothing
        End Try
    End Function
#End Region

#Region "Buscar"
    Public Function Buscar(ByVal Descricao As String, tipo As Integer) As DataTable
        Dim tab = New DataTable
        Dim cmd As SqlCommand
        Try
            cmd = New SqlCommand
            Select Case tipo
                Case 0
                    cmd.CommandText = "select * from VW_Pessoa where pes_codigo = @codigo"
                    cmd.Parameters.Add(New SqlParameter("@codigo", SqlDbType.Int)).Value = Val(Descricao)
                Case 1
                    cmd.CommandText = "select * from VW_Pessoa where pes_nome COLLATE SQL_Latin1_General_CP1_CI_AI like @descricao"
                    cmd.Parameters.Add(New SqlParameter("@descricao", SqlDbType.VarChar)).Value = "%" & Descricao & "%"
                Case Else
                    cmd.CommandText = "select * from VW_Pessoa order by pes_nome"
            End Select

            tab = ObjConexao.executaConsulta(cmd)
            tab.DefaultView.AllowNew = False
        Catch ex As Exception
            Throw ex
        Finally
            cmd = Nothing
        End Try
        Return tab
    End Function
#End Region

#Region "ValidaReplica"
    Function ValidaReplica(Codigo As Integer, Descricao As String) As Boolean
        Dim tab = New DataTable
        Dim cmd As SqlCommand
        Try
            cmd = New SqlCommand
            cmd.CommandText = "select * from VW_Pessoa where pes_nome=@descricao and pes_codigo<>@codigo "
            cmd.Parameters.Add(New SqlParameter("@codigo", SqlDbType.Int)).Value = Codigo
            cmd.Parameters.Add(New SqlParameter("@descricao", SqlDbType.VarChar)).Value = Descricao
            tab = ObjConexao.executaConsulta(cmd)
            tab.DefaultView.AllowNew = False
            If (tab.Rows.Count = 0) Then
                Return True
            Else
                Return False
            End If
        Catch ex As Exception
            Throw ex
        Finally
            cmd = Nothing
        End Try
    End Function
#End Region
End Class
